IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_ContentTypeSelectAll]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_ContentTypeSelectAll]
GO
create procedure [dbo].[cms_sp_ContentTypeSelectAll]
(
@SiteGroupId int,
@SiteId int
)
as
select 
	ContentTypes.ContentTypeId as Id,
    ContentTypes.SiteId,
	ContentTypes.SiteGroupId,
	ContentTypes.SystemLanguageId,
	ContentTypes.ContentTypeName,
	ContentTypes.Description,
	ContentTypes.IsSystemType,
	ContentTypes.CreatedBy,
    ContentTypes.DateCreated,
    ContentTypes.LastUpdatedBy,
    ContentTypes.DateLastUpdated,
	Creators.UserName as UserCreatedBy,
	Updaters.UserName as UserLastUpdatedBy
from [dbo].[ContentTypes]
left join [dbo].[Users] as Creators
	on Creators.UserId = ContentTypes.CreatedBy
left join [dbo].[Users] as Updaters
	on Updaters.UserId = ContentTypes.LastUpdatedBy
where (ContentTypes.SiteGroupId is null and ContentTypes.SiteId is null) or
	(ContentTypes.SiteGroupId = @SiteGroupId and ContentTypes.SiteId is null) or
	(ContentTypes.SiteGroupId = @SiteGroupId and ContentTypes.SiteId = @SiteId)


GO

